package com.kehutong.payfees.dao;

import org.coraframework.orm.annotation.Dao;
import org.coraframework.orm.annotation.Mapper;
import org.coraframework.orm.jdbc.Page;
import org.coraframework.orm.jdbc.execute.Condition;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Dao
public interface ChargeBillDao {

	@Mapper(
			genericType = HashMap.class,
			sql= "SELECT MIN(shouldPayDate) as minDate, MAX(shouldPayDate) as maxDate FROM `t_charge_bill`"
	)
	public Map<String, Object> getShouldPayDate(Condition... condition);

	@Mapper(
			genericType = HashMap.class,
			sql= "select yearMonth,year,month,receTotal,unPaidTotal,paidTotal,if(paidTotal = receTotal,'已缴清','未缴清') as 'paidState' from " +
					"(" +
					"select houseNo,DATE_FORMAT(shouldPayDate,'%Y-%m') as 'yearMonth',DATE_FORMAT(shouldPayDate,'%Y') as 'year', " +
					"DATE_FORMAT(shouldPayDate,'%m') as 'month',sum(total) as 'receTotal', " +
					"sum(if( paidState = 1, total, 0)) as 'unPaidTotal', " +
					"sum(if( paidState = 2, total, 0)) as 'paidTotal' " +
					"from t_charge_bill where deleted = false and enableState = 2 " +
					"GROUP BY houseNo,DATE_FORMAT(shouldPayDate,'%Y-%m'),DATE_FORMAT(shouldPayDate,'%Y'),DATE_FORMAT(shouldPayDate,'%m') " +
					"order BY houseNo,DATE_FORMAT(shouldPayDate,'%Y-%m') desc " +
					") A " +
					"where ${} "
	)
	public List<Map<String, Object>> getMergeBills(Condition... condition);

	@Mapper(
			genericType = HashMap.class,
			sql="select sum(if( paidState = 1, total, 0)) as 'unPaidTotal' from t_charge_bill where ${}"
	)
	public Map<String, Object> getUnPaidTotal(Condition... condition);

	@Mapper(
			genericType = HashMap.class,
			sql = "select id,houseNo,paidState,enableState,deleted,yearMonth from ( " +
					"select id,houseNo,paidState,enableState,deleted,DATE_FORMAT(shouldPayDate,'%Y-%m') as 'yearMonth' from t_charge_bill) t " +
					"where ${}"
	)
	public List<Map<String, Object>> getBillIds(Condition... condition);


	@Mapper(
			genericType = HashMap.class,
			sql = "select communityNo as id, communityName as name, shouldReceive, received, toReceive, ROUND(received / shouldReceive * 100, 2) ratio from (" +
					"select t.communityNo,t.communityName,sum(t.total) as shouldReceive,sum(if(t.paidState = 2, t.total,0)) received ,sum(if(t.paidState = 1, t.total,0)) toReceive from t_charge_bill t " +
					"where ${} " +
					"GROUP BY t.communityNo,t.communityName " +
					") a;"
	)
	public List<Map<String, Object>> getBillRatio(Condition... condition);
}
